insert overwrite table jms_dm.dm_outport_effect_summary_dt
select
    scan_date               --揽收/入仓日期
   ,sender_network_code     --寄件网点编码
   ,sender_network_name     --寄件网点名称
   ,sender_fran_code        --寄件加盟商编码
   ,sender_fran_name        --寄件加盟商名称
   ,sender_agent_code       --寄件代理区编码
   ,sender_agent_name       --寄件代理区名称
   ,count(case when if_zhund = 1 or if_liuc = 1 then waybill_no end) as  taking_cnt   --揽收票量
   ,count(case when if_zhund = 1 then waybill_no end) as  zhund_cnt    --准点票量
   ,count(case when if_liuc  = 1 then waybill_no end) as  liuc_cnt     --留仓票量
   ,count(case when if_weid  = 1 then waybill_no end) as  weid_cnt     --未到票量
   ,count(case when if_wand  = 1 then waybill_no end) as  wand_cnt     --晚到票量
   ,ordersource_code
   ,ordersource_name
   ,order_mark_zh
   ,dt
from jms_dm.dm_outport_effect_detail_dt
where dt = date_sub('{{ execution_date | cst_ds }}',1)
group by scan_date
        ,sender_network_code
        ,sender_network_name
        ,sender_fran_code
        ,sender_fran_name
        ,sender_agent_code
        ,sender_agent_name
        ,ordersource_code
        ,ordersource_name
        ,order_mark_zh
        ,dt
distribute by dt,pmod(hash(rand()),10);
